package mwa.cs.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import oracle.jdbc.pool.OracleDataSource;

/**
 * for INSERT, UPDATE, DELETE, SELECT
 */
public class Database {

	/**
	 * connection
	 */
	private Connection connection = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;

    /**
     * constructor to create connection to database
     */
    public Database() {
        try {
        	OracleDataSource voOracleDataSource = new OracleDataSource();
        	voOracleDataSource.setURL(DatabaseInfo.URL);
        	voOracleDataSource.setUser(DatabaseInfo.USER);
        	voOracleDataSource.setPassword(DatabaseInfo.PASSWORD);
			connection = voOracleDataSource.getConnection();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    
	/**
	 * for INSERT command
	 * 
	 * @param sql
	 * @param args
	 * @return
	 */
	public void insert(String sql, Object... args) {
		try {
			pstmt = connection.prepareStatement(sql);

			for (int i = 0; i < args.length; i++) {
				pstmt.setObject(i + 1, args[i]);
			}

			pstmt.executeUpdate();
			
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
	
	/**
	 * for UPDATE, DELETE command
	 * 
	 * @param sql
	 * @param args
	 */
	public void update(String sql, Object... args) {
		try {
			pstmt = connection.prepareStatement(sql);

			for (int i = 0; i < args.length; i++) {
				pstmt.setObject(i + 1, args[i]);
			}

			pstmt.executeUpdate();
			
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
	
	/**
	 * for SELECT command
	 * 
	 * @param sql
	 * @param args
	 * @return
	 */
	public Map<String, Object> querySingle(String sql, Object... args) {
		try {
			pstmt = connection.prepareStatement(sql);

			for (int i = 0; i < args.length; i++) {
				pstmt.setObject(i + 1, args[i]);
			}

			rs = pstmt.executeQuery();
			ResultSetMetaData md = rs.getMetaData();

			if (rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>();

				for (int i = 1; i <= md.getColumnCount(); i++) {
					map.put(md.getColumnLabel(i), rs.getObject(i));
				}

				return map;
			} else {
				return null;
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * for SELECT command
	 * 
	 * @param sql
	 * @param args
	 * @return
	 */
	public List<Map<String, Object>> queryList(String sql, Object... args) {
		try {
			List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

			pstmt = connection.prepareStatement(sql);

			for (int i = 0; i < args.length; i++) {
				pstmt.setObject(i + 1, args[i]);
			}

			rs = pstmt.executeQuery();
			ResultSetMetaData md = rs.getMetaData();

			while (rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>();

				for (int i = 1; i <= md.getColumnCount(); i++) {
					map.put(md.getColumnLabel(i), rs.getObject(i));
				}

				list.add(map);
			}

			return list;
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
	public List<Map<String, Object>> queryListDataTable(String sql, Object... args) {
		try {
			List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

			pstmt = connection.prepareStatement(sql);

			for (int i = 0; i < args.length; i++) {
				pstmt.setObject(i + 1, args[i]);
			}

			rs = pstmt.executeQuery();
			ResultSetMetaData md = rs.getMetaData();

			while (rs.next()) {
				Map<String, Object> map = new HashMap<String, Object>();

				for (int i = 1; i <= md.getColumnCount(); i++) {
					map.put(""+i, rs.getObject(i));
				}

				list.add(map);
			}

			return list;
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * for beginning transaction
	 */
	public void beginTransaction() {
		try {
			connection.setAutoCommit(false);
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
	
	/**
	 * commit transaction
	 */
	public void commit() {
		try {
			connection.commit();
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
	
	/**
	 * roll back transaction
	 */
	public void rollback() {
		try {
			connection.rollback();
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
	
	/**
	 * close connection
	 */
	public void close() {
		try {
			if (connection != null) {
				connection.close();
			}
			if (pstmt != null) {
				pstmt.close();
			}
			if (rs != null) {
				rs.close();
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
}
